﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections.Generic;

namespace DAL
{
    class DBAccess
    {


        public static string connString;
        public static string Route;


        #region Utilities

        private DBAccess()
        {
          throw new  InvalidOperationException();
        }

        //Returns the ConnectionString from the Web.Config file
        internal static string GetConnectionString()
        {
                var appSR = new AppSettingsReader();
                connString = "Data Source=W8;Initial Catalog=ObjetoCeleste;Integrated Security=True";
                //ConfigurationManager.ConnectionStrings["Espacio"].ConnectionString;
                return connString; 
        }  //Data Source=WILLIAM-PC;Initial Catalog=ObjetoCeleste;Integrated Security=True
           //Data Source=WILLIAM_LAPTOP\\SQLSERVER2014;Initial Catalog=ObjetoCeleste;Integrated Security=True
        //Returns the SQLConnection
        internal static SqlConnection GetConnection()
        {
            
                if (connString == "" || connString == null)
                    connString = GetConnectionString();
                
                return new SqlConnection(connString);
            
        }

        //Returns an initialized SqlCommand
        internal static IDbCommand GetCommand()
        {
            return new SqlCommand();
        }

        #endregion


        #region SQL Methods

        //Executes SQL code that doesn't return anything.
        internal static void ExecuteNonQuery(string pSQL)
        {

                SqlCommand cmd = new SqlCommand(pSQL, GetConnection());
                cmd.Connection.Open();
                cmd.CommandTimeout = 0;
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
        }

        //Executes SQL code that returns an IDataReader implemented class
        internal static IDataReader ExecuteQuery(string pSQL, bool dif)
        {

                SqlCommand cmd = new SqlCommand(pSQL, GetConnection());
                cmd.Connection.Open();
                cmd.CommandTimeout = 0;

                return cmd.ExecuteReader();

        }

        //Executes SQL code that returns a DataSet
        internal static DataSet ExecuteQuery(SqlCommand pCmd)
        {
            
                SqlDataAdapter adp;
                DataSet tempDataset = new DataSet();

                pCmd.Connection = GetConnection();
                pCmd.CommandTimeout = 0;

                adp = new SqlDataAdapter(pCmd);
                adp.Fill(tempDataset);

                pCmd.Connection.Close();

                return tempDataset;
            
            
        }


        //Executes the SP but does'nt return anything
        internal static void ExecuteSP(SqlCommand pCmd, string pName)
        {
            
                pCmd.Connection = GetConnection();
                pCmd.CommandText = pName;
                pCmd.CommandType = CommandType.StoredProcedure;
                pCmd.Connection.Open();
                pCmd.ExecuteReader();
                pCmd.Connection.Close();
            
          
        }

        //Executes the SP but does'nt return anything
        internal static void ExecuteSPNonQuery(SqlCommand pCmd, string pName)
        {
            
                pCmd.Connection = GetConnection();
                pCmd.CommandText = pName;
                pCmd.CommandType = CommandType.StoredProcedure;
                pCmd.Connection.Open();
                pCmd.ExecuteNonQuery();
                pCmd.Connection.Close();
            
            
        }

        //Executes the SP and returns a filled dataset
        internal static DataSet ExecuteSPWithDS(ref SqlCommand pCmd, string pName)
        {
           
                SqlDataAdapter adp;
                DataSet tempDataset = new DataSet();

                pCmd.Connection = GetConnection();
                pCmd.CommandText = pName;
                pCmd.CommandType = CommandType.StoredProcedure;
                pCmd.CommandTimeout = 0;

                adp = new SqlDataAdapter(pCmd);
                adp.Fill(tempDataset);

                pCmd.Connection.Close();

                return tempDataset;
           
        }

        #endregion
    }
}

